/**
 * 相册数据模型
 * 负责相册相关的数据库操作
 */

import { dbConnection } from '../connection'
import { Album, AlbumListResponse } from '../../../renderer/types'

/**
 * 相册模型类
 */
export class AlbumModel {
  /**
   * 创建新相册
   * @param album 相册数据
   * @returns 创建的相册ID
   */
  static create(album: Omit<Album, 'id'>): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare(`
      INSERT INTO albums (name, description, cover_photo_id, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?)
    `)
    
    const now = new Date().toISOString()
    const result = stmt.run(
      album.name,
      album.description,
      album.cover_photo_id,
      now,
      now
    )
    
    return result.lastInsertRowid as number
  }

  /**
   * 根据ID获取相册
   * @param id 相册ID
   * @returns 相册数据
   */
  static getById(id: number): Album | undefined {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT * FROM albums WHERE id = ?')
    return stmt.get(id) as Album | undefined
  }

  /**
   * 获取相册列表
   * @param page 页码
   * @param limit 每页数量
   * @returns 相册列表和分页信息
   */
  static getList(page: number = 1, limit: number = 50): AlbumListResponse {
    const db = dbConnection.getConnection()
    
    // 获取总数
    const countStmt = db.prepare('SELECT COUNT(*) as total FROM albums')
    const { total } = countStmt.get() as { total: number }
    
    // 获取相册列表
    const offset = (page - 1) * limit
    const listStmt = db.prepare(`
      SELECT * FROM albums 
      ORDER BY updated_at DESC
      LIMIT ? OFFSET ?
    `)
    
    const albums = listStmt.all(limit, offset) as Album[]
    
    return {
      albums,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    }
  }

  /**
   * 更新相册信息
   * @param id 相册ID
   * @param updates 更新数据
   * @returns 是否更新成功
   */
  static update(id: number, updates: Partial<Album>): boolean {
    const db = dbConnection.getConnection()
    
    const fields = Object.keys(updates).filter(key => key !== 'id')
    if (fields.length === 0) return false
    
    // 添加更新时间
    updates.updated_at = new Date().toISOString()
    
    const setClause = fields.map(field => `${field} = ?`).join(', ')
    const values = fields.map(field => (updates as any)[field])
    
    const stmt = db.prepare(`
      UPDATE albums 
      SET ${setClause}
      WHERE id = ?
    `)
    
    const result = stmt.run(...values, id)
    return result.changes > 0
  }

  /**
   * 删除相册
   * @param id 相册ID
   * @returns 是否删除成功
   */
  static delete(id: number): boolean {
    const db = dbConnection.getConnection()
    
    // 使用事务确保数据一致性
    const transaction = db.transaction(() => {
      // 删除相册与照片的关联
      const deletePhotosStmt = db.prepare('DELETE FROM photo_albums WHERE album_id = ?')
      deletePhotosStmt.run(id)
      
      // 删除相册
      const deleteAlbumStmt = db.prepare('DELETE FROM albums WHERE id = ?')
      const result = deleteAlbumStmt.run(id)
      
      return result.changes > 0
    })
    
    return transaction()
  }

  /**
   * 向相册添加照片
   * @param albumId 相册ID
   * @param photoId 照片ID
   * @returns 是否添加成功
   */
  static addPhoto(albumId: number, photoId: number): boolean {
    const db = dbConnection.getConnection()
    
    // 检查是否已存在
    const checkStmt = db.prepare('SELECT COUNT(*) as count FROM photo_albums WHERE album_id = ? AND photo_id = ?')
    const { count } = checkStmt.get(albumId, photoId) as { count: number }
    
    if (count > 0) return false
    
    const stmt = db.prepare(`
      INSERT INTO photo_albums (album_id, photo_id, added_at)
      VALUES (?, ?, ?)
    `)
    
    const result = stmt.run(albumId, photoId, new Date().toISOString())
    
    // 更新相册的更新时间
    if (result.changes > 0) {
      this.update(albumId, { updated_at: new Date().toISOString() })
    }
    
    return result.changes > 0
  }

  /**
   * 从相册移除照片
   * @param albumId 相册ID
   * @param photoId 照片ID
   * @returns 是否移除成功
   */
  static removePhoto(albumId: number, photoId: number): boolean {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('DELETE FROM photo_albums WHERE album_id = ? AND photo_id = ?')
    const result = stmt.run(albumId, photoId)
    
    // 更新相册的更新时间
    if (result.changes > 0) {
      this.update(albumId, { updated_at: new Date().toISOString() })
    }
    
    return result.changes > 0
  }

  /**
   * 获取相册中的照片
   * @param albumId 相册ID
   * @param page 页码
   * @param limit 每页数量
   * @returns 照片列表和分页信息
   */
  static getPhotos(albumId: number, page: number = 1, limit: number = 50) {
    const db = dbConnection.getConnection()
    
    // 获取总数
    const countStmt = db.prepare('SELECT COUNT(*) as total FROM photo_albums WHERE album_id = ?')
    const { total } = countStmt.get(albumId) as { total: number }
    
    // 获取照片列表
    const offset = (page - 1) * limit
    const listStmt = db.prepare(`
      SELECT p.* FROM photos p
      INNER JOIN photo_albums pa ON p.id = pa.photo_id
      WHERE pa.album_id = ?
      ORDER BY pa.added_at DESC
      LIMIT ? OFFSET ?
    `)
    
    const photos = listStmt.all(albumId, limit, offset)
    
    return {
      photos,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    }
  }

  /**
   * 获取相册数量
   * @returns 相册总数
   */
  static getCount(): number {
    const db = dbConnection.getConnection()
    
    const stmt = db.prepare('SELECT COUNT(*) as total FROM albums')
    const { total } = stmt.get() as { total: number }
    
    return total
  }
}